package com.xjgc.sap.ws.dao;

import com.xjgc.common.config.DbConn;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

/**
 * 回传-成品入库信息表
 * @author Administrator
 *
 */
@Component
public class ZxjM2Scprk {
	
	public String cprk(DbConn db){
		
		String sql = "select aufnr,werks,bwart,lgort,erfmg,posnr,cpudt_mkpf,budat_mkpf,sgtxt from zxj_m2s_cprk t where t.status = '0'";
		String updateSql = "update zxj_m2s_cprk set status = '1' where aufnr=? and werks=? and posnr=?";
		
		PreparedStatement pst = null;
		PreparedStatement updatePst = null;
		ResultSet rs = null;
		String result = null;
//		DbConn db = new DbConn();
		int count = 0;
		Document doc = DocumentHelper.createDocument();
		
		try {
			pst = db.getConnetion().prepareStatement(sql);
			rs = pst.executeQuery();
			updatePst = db.getConnetion().prepareStatement(updateSql);

			//增加根节点
			Element requestData = doc.addElement("REQUEST_DATA");
			while(rs.next()) {
				count++;
				Element item = requestData.addElement("ITEM");
				
			    Element aufnr = item.addElement("AUFNR");
			    aufnr.setText(rs.getString("aufnr")==null?"":rs.getString("aufnr"));
			    
			    Element werks = item.addElement("WERKS");
			    werks.setText(rs.getString("werks")==null?"":rs.getString("werks"));
			    
			    Element bwart = item.addElement("BWART");
			    bwart.setText(rs.getString("bwart")==null?"":rs.getString("bwart"));
			    
			    Element lgort = item.addElement("LGORT");
			    lgort.setText(rs.getString("lgort")==null?"":rs.getString("lgort"));
			    
			    Element erfmg = item.addElement("ERFMG");
			    erfmg.setText(rs.getString("erfmg")==null?"":rs.getString("erfmg"));
			    
			    Element posnr = item.addElement("POSNR");
			    posnr.setText(rs.getString("posnr")==null?"":rs.getString("posnr"));
			    
			    Element cpudtMkpf = item.addElement("CPUDT_MKPF");
			    cpudtMkpf.setText(rs.getString("cpudt_mkpf")==null?"":rs.getString("cpudt_mkpf"));
			    
			    Element budatMkpf = item.addElement("BUDAT_MKPF");
			    budatMkpf.setText(rs.getString("budat_mkpf")==null?"":rs.getString("budat_mkpf"));
			    
			    Element sgtxt = item.addElement("SGTXT");
			    sgtxt.setText(rs.getString("sgtxt")==null?"":rs.getString("sgtxt"));
			    
				updatePst.setString(1,rs.getString("aufnr"));
				updatePst.setString(2, rs.getString("werks"));
				updatePst.setString(3, rs.getString("posnr"));
				
				updatePst.execute();
			}
			updatePst.close();
			rs.close();
			pst.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.closeCn();
		}
		if(count != 0) {
			result = doc.asXML();
		}
		return result ;
	}
	
	public String eUpdate(String xml,DbConn db){
		
		PreparedStatement updatePst = null;
		PreparedStatement inPst = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String eDate = sdf.format(new Date());
//		DbConn db = new DbConn();
		
		//定义执行SQL脚本
		String updateSql = "update zxj_m2s_cprk set status = '0' where aufnr=? and werks=? and posnr=?";
		String inSql = "insert into zxj_opt_log (tablename,umk,date,remark) values(?,?,?,?)";
		
		//创建数据库连接
		int count = 0;
		try {
			updatePst = db.getConnetion().prepareStatement(updateSql);
			inPst = db.getConnetion().prepareStatement(inSql);
			//解析xml文件
			Document doc = null;
			try {
				doc = DocumentHelper.parseText(xml);
				Element root = doc.getRootElement();
				Iterator<?> it = root.elementIterator();

				while (it.hasNext()) {
					count++;
					Element element = (Element) it.next();

					updatePst.setString(1, element.elementText("AUFNR"));
					updatePst.setString(2, element.elementText("WERKS"));
					updatePst.setString(3, element.elementText("POSNR"));
					
					inPst.setString(1, "ZXJ_M2S_CPRK");
					inPst.setString(2, element.elementText("AUFNR")+","+element.elementText("WERKS")+","+element.elementText("POSNR"));
					inPst.setString(3, eDate);
					inPst.setString(4, "SAP系统接收数据失败。");

					//更新数据
					updatePst.execute();
					inPst.execute();
				}

			} catch (DocumentException e1) {
				e1.printStackTrace();
			}
			updatePst.close();
			inPst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			db.closeCn();
		}
		return count+"";
	}
}
